package com.aizhixin.lab.statistics.service;

import com.aizhixin.lab.common.core.ApiReturnConstants;
import com.aizhixin.lab.common.core.DataValidity;
import com.aizhixin.lab.common.core.LabReportCore;
import com.aizhixin.lab.common.core.PageUtil;
import com.aizhixin.lab.common.domain.PageDomain;
import com.aizhixin.lab.common.dto.SortDTO;
import com.aizhixin.lab.common.utils.PageJdbcUtil;
import com.aizhixin.lab.company.entity.Classes;
import com.aizhixin.lab.company.entity.User;
import com.aizhixin.lab.company.repository.ClassesRepository;
import com.aizhixin.lab.company.repository.UserRepository;
import com.aizhixin.lab.company.service.ClassesService;
import com.aizhixin.lab.course.classes.dto.LabClassDTO;
import com.aizhixin.lab.course.classes.entity.CourseClass;
import com.aizhixin.lab.course.classes.entity.CourseClassStu;
import com.aizhixin.lab.course.classes.repository.CourseClassRepository;
import com.aizhixin.lab.course.classes.repository.CourseClassStuRepository;
import com.aizhixin.lab.course.course.entity.Course;
import com.aizhixin.lab.course.course.entity.CourseChapter;
import com.aizhixin.lab.course.course.entity.CourseChapterFile;
import com.aizhixin.lab.course.course.repository.CourseChapterFileRepository;
import com.aizhixin.lab.course.course.repository.CourseChapterRepository;
import com.aizhixin.lab.course.course.repository.CourseRepository;
import com.aizhixin.lab.course.course.service.CourseService;
import com.aizhixin.lab.project.course.core.TaskStatus;
import com.aizhixin.lab.project.course.domain.KeyValueDomain;
import com.aizhixin.lab.project.template.core.TaskFileType;
import com.aizhixin.lab.statistics.domain.*;
import com.aizhixin.lab.statistics.util.ReportUtils;
import com.aizhixin.lab.task.core.TaskInforCode;
import com.aizhixin.lab.task.domain.ClassGradeDomain;
import com.aizhixin.lab.task.entity.ReleaseTask;
import com.aizhixin.lab.task.entity.TaskStuInfor;
import com.aizhixin.lab.task.repository.ReleaseTaskRepository;
import com.aizhixin.lab.task.repository.TaskStuInforRepository;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
@Service
@Transactional
public class TeacherDataService {
    @Autowired
    private PageJdbcUtil pageJdbcUtil;
    @Autowired
    private CourseChapterRepository chapterRepository;
    @Autowired
    private CourseRepository courseRepository;
    @Autowired
    private ClassesService classesService;
    @Autowired
    private CourseService courseService;
    @Autowired
    private CourseChapterFileRepository chapterFileRepository;
    @Autowired
    private UserRepository userRepository;
    @Autowired
    private TaskStuInforRepository inforRepository;
    @Autowired
    private CourseClassStuRepository stuRepository;
    @Autowired
    private CourseClassRepository classRepository;
    @Autowired
    private ClassesRepository classesRepository;
    @Autowired
    private ReleaseTaskRepository releaseTaskRepository;

    public List<ClassGradeDomain> courseResult(String courseId, Boolean isComplexTable) {
        Course course = courseService.findById(courseId);
        List<ClassGradeDomain> classGradeDomainList = new ArrayList<>();
        List<CourseClass> courseClassList = classRepository.findAllByDeleteFlagAndCourseId(DataValidity.VALID.getState(), courseId);
        for (CourseClass courseClass : courseClassList) {
            List<CourseClassStu> courseClassStuList = stuRepository.findAllByDeleteFlagAndCourseClassId(DataValidity.VALID.getState(), courseClass.getId());
            for (CourseClassStu courseClassStu : courseClassStuList) {
                ClassGradeDomain classGradeDomain = new ClassGradeDomain();
                List<KeyValueDomain> keyValueDomains = new ArrayList<>();
                List<TaskStuInfor> taskStuInfoList = inforRepository.findByCourseClassIdAndStuIdAndDeleteFlagAndTaskStatus(courseClass.getId(), courseClassStu.getStuId(), DataValidity.VALID.getState(), TaskInforCode.TASK_STATUS_APPROVED);
                if (taskStuInfoList.size() > 0) {
                    Integer sum = 0;
                    for (TaskStuInfor taskStuInfor : taskStuInfoList) {
                        if (isComplexTable) {
                            ReleaseTask one = releaseTaskRepository.findOne(taskStuInfor.getReleaseTaskId());
                            if (!Objects.isNull(one)) {
                                KeyValueDomain keyValueDomain = new KeyValueDomain();
                                keyValueDomain.setKey(one.getTaskName());
                                keyValueDomain.setValue(Double.parseDouble(taskStuInfor.getGrade()));
                                keyValueDomains.add(keyValueDomain);
                            }

                        }
                        sum += Integer.parseInt(taskStuInfor.getGrade());
                    }

                    classGradeDomain.setTaskGrade(sum / taskStuInfoList.size());
                } else {
                    classGradeDomain.setTaskGrade(0);
                }
                classGradeDomain.setKeyValueDomains(keyValueDomains);
                String truantSql = "SELECT COUNT(c.id) FROM c_stu_sign_in c LEFT JOIN c_release_sign_in r ON c.sign_in_id=r.id WHERE r.course_id='" + courseId + "' AND c.stu_id=" + courseClassStu.getStuId() + " AND c.status=0";
                String lateSql = "SELECT COUNT(c.id) FROM c_stu_sign_in c LEFT JOIN c_release_sign_in r ON c.sign_in_id=r.id WHERE r.course_id='" + courseId + "' AND c.stu_id=" + courseClassStu.getStuId() + " AND c.status=2";
                Integer lateNum = pageJdbcUtil.getCountInt(lateSql);
                Integer truantNum = pageJdbcUtil.getCountInt(truantSql);
                classGradeDomain.setSignGrade(100 - truantNum * 3 - lateNum);
                User user = userRepository.findOne(courseClassStu.getStuId());
                Classes classes = classesRepository.findById(user.getClassId());
                classGradeDomain.setUserName(user.getName());
                classGradeDomain.setCourseName(course.getName());
                classGradeDomain.setClassName(classes.getName());
                classGradeDomainList.add(classGradeDomain);
            }
        }
        return classGradeDomainList;
    }


    /**
     * 老师总体数据统计
     *
     * @param id
     * @return
     */
    public TeacherDataDomain teacherData(Long id) {
        TeacherDataDomain domain = new TeacherDataDomain();
        String sql = "SELECT COUNT(DISTINCT t.CLASS_ID) FROM t_course_class t LEFT JOIN c_course cc ON t.course_id=cc.id WHERE t.delete_flag=0 AND t.teacher_id=" + id + " AND cc.is_archive=0 AND cc.delete_flag=0";
        domain.setClassNum(pageJdbcUtil.getCountInt(sql));
        sql = "SELECT COUNT(DISTINCT tcs.stu_id) FROM t_course_class_stu tcs LEFT JOIN t_course_class tc ON tc.id=tcs.course_class_id LEFT JOIN c_course cc ON tc.course_id=cc.id WHERE tcs.delete_flag=0 AND tc.teacher_id=" + id + " AND cc.is_archive=0 AND cc.delete_flag=0 AND tc.delete_flag=0";
        domain.setStudentNum(pageJdbcUtil.getCountInt(sql));
        sql = "SELECT COUNT(*) FROM t_lab_report t LEFT JOIN c_course cc ON t.course_id=cc.id WHERE t.DELETE_FLAG=0 AND t.REVIEW_RESULT not in('init','unchecked','uncommit') AND cc.is_archive=0 AND cc.delete_flag=0 AND t.TEACHER_ID=" + id;
        domain.setReportNum(pageJdbcUtil.getCountInt(sql));
        sql = "SELECT COUNT(*) FROM t_release_task t LEFT JOIN c_course cc ON t.course_id=cc.id WHERE t.DELETE_FLAG=0 AND cc.is_archive=0 AND cc.delete_flag=0 AND t.CREATED_BY=" + id;
        domain.setReleaseTaskNum(pageJdbcUtil.getCountInt(sql));
        sql = "SELECT COUNT(*) FROM t_task_stu_infor t LEFT JOIN t_release_task tr on tr.ID=t.release_task_id LEFT JOIN c_course cc ON tr.course_id=cc.id WHERE t.task_status='approved' AND t.delete_flag=0 AND cc.is_archive=0 AND cc.delete_flag=0 AND tr.CREATED_BY=" + id;
        domain.setApprovedTaskNum(pageJdbcUtil.getCountInt(sql));
        sql = "SELECT COUNT(DISTINCT t.COURSE_ID,t.CHAPER_ID) FROM t_lab_report t LEFT JOIN c_course cc ON t.course_id=cc.id WHERE t.DELETE_FLAG=0 AND t.REVIEW_RESULT not in('init','uncommit') AND cc.is_archive=0 AND cc.delete_flag=0 AND t.TEACHER_ID=" + id;
        domain.setCourseTime(pageJdbcUtil.getCountInt(sql));
        return domain;
    }

    /**
     * 获取班级学生人数
     *
     * @param classId
     * @return
     */

    RowMapper<Long> num = new RowMapper<Long>() {

        @Override
        public Long mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            return rs.getLong("id");
        }
    };

    public List<Long> getClassStuId(Long classId) {
        String sql = "SELECT t.id FROM t_user t WHERE t.delete_flag=0 AND t.CLASS_ID=" + classId;
        return pageJdbcUtil.getInfo(sql, num);
    }

    /**
     * 获取班级总在线时长
     *
     * @param
     * @return
     */
    public Integer getStuTotalOnlineTime(Long id, Integer day) {
        String sql = "SELECT COALESCE(SUM(TIMESTAMPDIFF(MINUTE,t.CREATED_DATE,t.LAST_MODIFIED_DATE)),0) totalTime FROM t_log t WHERE t.DELETE_FLAG=0 AND DATE_SUB(NOW(),INTERVAL " + day + " DAY)<t.CREATED_DATE AND t.CREATED_BY in (SELECT t.id FROM t_user t WHERE t.delete_flag=0 AND t.CLASS_ID=" + id + ")";
        return pageJdbcUtil.getCountInt(sql);
    }


    RowMapper<ClassDataDomain> cd = new RowMapper<ClassDataDomain>() {

        @Override
        public ClassDataDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ClassDataDomain domain = new ClassDataDomain();
            domain.setClassId(rs.getLong("id"));
            domain.setClassName(rs.getString("class_name"));
            domain.setStudentNum(getClassStuId(domain.getClassId()).size());
            if (domain.getStudentNum() > 0 && domain.getStudentNum() != null) {
                domain.setAvgOnlineTime(getStuTotalOnlineTime(domain.getClassId(), 180) / domain.getStudentNum());
            } else {
                domain.setAvgOnlineTime(0);
            }
            domain.setVitality(getClassVitality(domain.getAvgOnlineTime()));

            return domain;
        }
    };

    /**
     * 班级数据统计
     *
     * @param id
     */
    public List<ClassDataDomain> getClassData(Long id) {
        String sql = "SELECT DISTINCT tc.ID id,tc.`NAME` class_name FROM t_course_class tcc JOIN t_classes tc ON tc.ID=tcc.CLASS_ID LEFT JOIN c_course cc ON tcc.course_id=cc.id WHERE tcc.teacher_id=" + id + " AND cc.is_archive=0 AND cc.delete_flag=0 AND tcc.delete_flag=0 ORDER BY tc.ID ASC";
        return pageJdbcUtil.getInfo(sql, cd);
    }

    /**
     * 获取报告提交数量
     *
     * @param courseId
     * @return
     */
    public Integer getReportCommitNum(String courseId, Long classId) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.commit_time IS not Null AND t.delete_flag=0 AND t.COURSE_ID='" + courseId + "' AND t.CLASS_ID=" + classId;
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 获取任务提交数量
     *
     * @param courseId
     * @return
     */
    public Integer getTaskCommitNum(String courseId, Long classId) {
        String sql = "SELECT count(*) FROM t_task_stu_infor t LEFT JOIN t_release_task tr ON tr.ID=t.release_task_id LEFT JOIN c_course cc ON cc.id=tr.COURSE_ID WHERE t.delete_flag=0 AND t.task_status in ('approved','pending') AND cc.id='" + courseId + "' AND tr.CLASS_ID=" + classId;
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 查询教学进度
     *
     * @param courseId
     * @return
     */
    public Integer courseProgress(String courseId, Long classId) {
        Integer integer = chapterRepository.countByCourseIdAndIsExpAndDeleteFlag(courseId, true, DataValidity.VALID.getState());
        String sql = "SELECT COUNT(DISTINCT t.CHAPER_ID) FROM t_lab_report t WHERE t.COURSE_ID='" + courseId + "' AND t.REVIEW_RESULT NOT in('init','uncommit') and t.CLASS_ID=" + classId;
        Integer integer1 = pageJdbcUtil.getCountInt(sql);
        if (integer != 0) {
            return integer1 * 100 / integer;
        } else {
            return 0;
        }
    }

    RowMapper<TeacherCourseDomain> tc = new RowMapper<TeacherCourseDomain>() {

        @Override
        public TeacherCourseDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            TeacherCourseDomain domain = new TeacherCourseDomain();
            domain.setId(rs.getString("id"));
            domain.setCourseName(rs.getString("course_name"));
            Long classId = rs.getLong("class_id");
            domain.setReportCommitNum(getReportCommitNum(domain.getId(), classId));
            domain.setTaskCommitNum(getTaskCommitNum(domain.getId(), classId));
            domain.setCourseCase(courseProgress(domain.getId(), classId));
            return domain;
        }
    };

    /**
     * 班级实验课数据
     *
     * @param id
     * @return
     */
    public List<TeacherCourseDomain> getCourseData(Long id, Long classId) {
        String sql = "SELECT cc.id id,cc.`name` course_name,cc.created_by created_by,tcc.CLASS_ID class_id FROM c_course cc LEFT JOIN t_course_class tcc ON cc.id=tcc.course_id WHERE cc.is_archive=0 AND cc.delete_flag=0 AND tcc.delete_flag=0 AND cc.is_publish=1 AND cc.type=20 AND tcc.teacher_id=" + id + " AND tcc.CLASS_ID=" + classId;
        List<TeacherCourseDomain> info = pageJdbcUtil.getInfo(sql, tc);
        return info;

    }

    /**
     * 获取章节报告提交数
     *
     * @param chapterId
     * @return
     */
    public Integer getChapterReportCommitNum(Long chapterId) {
        String sql = "SELECT COUNT(*) FROM t_lab_report t WHERE t.DELETE_FLAG=0 AND t.COMMIT_TIME IS NOT NULL AND t.CHAPER_ID in(SELECT cc.id FROM c_course_chapter cc WHERE cc.id=" + chapterId + " or cc.parent_id=" + chapterId + ")";
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 章节报告总数
     *
     * @param chapterId
     * @return
     */
    public Integer getChapterReportTotalNum(Long chapterId) {
        String sql = "SELECT count(*) FROM t_lab_report tlr WHERE tlr.DELETE_FLAG=0 AND  tlr.CHAPER_ID in(SELECT cc.id FROM c_course_chapter cc WHERE cc.id=" + chapterId + " or cc.parent_id=" + chapterId + ")";
        return pageJdbcUtil.getCountInt(sql);
    }

    /**
     * 实验报告提交情况统计
     *
     * @param courseId
     */
    RowMapper<ChapterReportDomain> tr = new RowMapper<ChapterReportDomain>() {

        @Override
        public ChapterReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ChapterReportDomain domain = new ChapterReportDomain();
            domain.setChapterName(rs.getString("chapter_name"));
            domain.setReportScore(rs.getInt("commit_num"));
            return domain;
        }
    };

    public List<ChapterReportDomain> getChapterReport(String courseId, Long classId) {
//        List<CourseChapter> courseChapters = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> chapterReportDomains = new ArrayList<>();
//        for (CourseChapter c : courseChapters) {
//            ChapterReportDomain chapterReportDomain = new ChapterReportDomain();
//            chapterReportDomain.setChapterName(c.getName());
//            chapterReportDomain.setReportScore(getChapterReportCommitNum(c.getId()));
//            chapterReportDomains.add(chapterReportDomain);
//        }
//        return chapterReportDomains;
        String sql = "select lc.COURSE_ID,lc.COURSE_CLASS_ID,lc.CHAPER_ID,c.`name` as course_name,cl.name as class_name,cc.`name` as chapter_name,(select count(*) from `t_user` u where u.delete_flag =0 and u.CLASS_ID =lc.CLASS_ID) as class_stu_num,lc.STU_NUM,(select count(*) from `t_lab_report` lr where lr.DELETE_FLAG =0 and COMMIT_TIME is not null and lr.COURSE_CLASS_ID=lc.COURSE_CLASS_ID and lr.CHAPER_ID =lc.CHAPER_ID) as commit_num from `t_lab_class` lc LEFT JOIN `c_course` c ON lc.COURSE_ID = c.id LEFT JOIN `t_classes` cl ON lc.CLASS_ID = cl.ID LEFT JOIN `c_course_chapter` cc ON cc.id = lc.CHAPER_ID where lc.DELETE_FLAG =0 and lc.COURSE_ID='" + courseId + "' AND lc.CLASS_ID=" + classId;
        List<ChapterReportDomain> info = pageJdbcUtil.getInfo(sql, tr);
        return info;
    }

    RowMapper<String> rs = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            return rs.getString("result");
        }
    };

    /**
     * 获取章节的平均成绩
     *
     * @param chapterId
     * @return
     */
    public ChapterReportDomain getChapterAvgScore(Long chapterId) {
        String sql = "SELECT t.REVIEW_RESULT result FROM t_lab_report t WHERE t.REVIEW_RESULT NOT in('init','uncommit','unchecked') and t.DELETE_FLAG=0 AND t.CHAPER_ID=" + chapterId;
        List<String> results = pageJdbcUtil.getInfo(sql, rs);
        ChapterReportDomain domain = new ChapterReportDomain();
        int sum = 0;
        for (String s : results) {
            Integer score = ReportUtils.getScore(s);
            if (domain.getReportMaxScore() == null || domain.getReportMaxScore() < score) {
                domain.setReportMaxScore(score);
            }
            if (domain.getReportMinScore() == null || domain.getReportMinScore() > score) {
                domain.setReportMinScore(score);
            }
            sum += score;
        }
        if (results.size() > 0) {
            domain.setReportScore(sum / results.size());
        }
        return domain;
    }

    /**
     * 实验报告成绩统计
     *
     * @param courseId
     * @return
     */
    RowMapper<ChapterReportDomain> chapterReport = new RowMapper<ChapterReportDomain>() {

        @Override
        public ChapterReportDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            ChapterReportDomain result = getChapterReportScore(rs.getString("result"));
            result.setChapterName(rs.getString("chapter_name"));
            return result;
        }
    };

    public List<ChapterReportDomain> getReportScore(String courseId, Long classId) {
//        List<CourseChapter> courseChapters = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> chapterReportDomains = new ArrayList<>();
//        for (CourseChapter c : courseChapters) {
//            ChapterReportDomain chapterAvgScore = getChapterAvgScore(c.getId());
//            chapterReportDomains.add(chapterAvgScore);
//        }
//        return chapterReportDomains;
        String sql = "select cc.`name` as chapter_name,(SELECT GROUP_CONCAT(t.REVIEW_RESULT) FROM t_lab_report t WHERE t.CHAPER_ID=cc.id AND t.REVIEW_RESULT not in('init','uncommit','unchecked') AND t.COURSE_CLASS_ID=lc.COURSE_CLASS_ID ) as result from `t_lab_class` lc LEFT JOIN `c_course_chapter` cc ON cc.id = lc.CHAPER_ID where lc.DELETE_FLAG =0 AND cc.is_exp=1 and lc.COURSE_ID='" + courseId + "' AND lc.CLASS_ID=" + classId;
        return pageJdbcUtil.getInfo(sql, chapterReport);
    }

    public ChapterReportDomain getChapterReportScore(String result) {
        if (result == null || "".equals(result)) {
            ChapterReportDomain domain = new ChapterReportDomain();
            domain.setReportMaxScore(0);
            domain.setReportScore(0);
            domain.setReportMinScore(0);
            return domain;
        }
        String[] split = result.split(",");
        ChapterReportDomain domain = new ChapterReportDomain();
        if (split.length > 0) {
            Integer sum = 0;
            for (String s : split) {
                Integer score = ReportUtils.getScore(s);
                if (domain.getReportMaxScore() == null || domain.getReportMaxScore() < score) {
                    domain.setReportMaxScore(score);
                }
                if (domain.getReportMinScore() == null || domain.getReportMinScore() > score) {
                    domain.setReportMinScore(score);
                }
                sum += score;
            }
            domain.setReportScore(sum / split.length);
        } else {
            domain.setReportMaxScore(0);
            domain.setReportScore(0);
            domain.setReportMinScore(0);
        }
        return domain;
    }

    /**
     * 获取章节任务提交数量
     *
     * @param chapterId
     * @return
     */
    public Integer getChapterTaskCommitNum(Long chapterId) {
        String sql = "SELECT count(*) FROM t_task_stu_infor tt WHERE tt.delete_flag=0 AND tt.COURSE_CHAPTER_ID=" + chapterId + " AND tt.commit_time is NOT NULL";
        Integer countInt = pageJdbcUtil.getCountInt(sql);
        return countInt;
    }

    /**
     * 获取章节任务总数
     *
     * @param chapterId
     * @return
     */
    public Integer getChapterTaskTotal(Long chapterId) {
        String sql = "SELECT count(*) FROM t_task_stu_infor tt WHERE tt.delete_flag=0 AND tt.COURSE_CHAPTER_ID=" + chapterId;
        Integer countInt = pageJdbcUtil.getCountInt(sql);
        return countInt;
    }

    /**
     * 章节任务成绩统计
     *
     * @param chapterId
     */
    public ChapterReportDomain getChapterTaskScore(Long chapterId) {
        String sql = "SELECT tt.result result FROM t_task_stu_infor tt WHERE tt.delete_flag=0 AND tt.COURSE_CHAPTER_ID=" + chapterId + " AND tt.task_status='approved'";
        List<String> results = pageJdbcUtil.getInfo(sql, rs);
        ChapterReportDomain domain = new ChapterReportDomain();
        int sum = 0;
        for (String s : results) {
            Integer score = ReportUtils.getScore(s);
            if (domain.getReportMaxScore() == null || domain.getReportMaxScore() < score) {
                domain.setReportMaxScore(score);
            }
            if (domain.getReportMinScore() == null || domain.getReportMinScore() > score) {
                domain.setReportMinScore(score);
            }
            sum += score;
        }
        if (results.size() > 0) {
            domain.setReportScore(sum / results.size());
        }
        return domain;
    }

    /**
     * 获取章节任务提交数
     *
     * @param courseId
     * @return
     */
    public List<ChapterReportDomain> getChapterTaskCommitNum(String courseId, Long classId) {
//        List<CourseChapter> courseChapterList = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> domains = new ArrayList<>();
//        for (CourseChapter c : courseChapterList) {
//            ChapterReportDomain domain = new ChapterReportDomain();
//            domain.setChapterName(c.getName());
//            domain.setReportScore(getChapterTaskCommitNum(c.getId()));
//            domains.add(domain);
//
//        }
//        return domains;
        String sql = "SELECT trt.TASK_NAME chapter_name,(SELECT COUNT(*) FROM t_task_stu_infor ttsi WHERE ttsi.commit_time is not NULL AND ttsi.release_task_id=trt.id) commit_num FROM t_release_task trt  WHERE trt.DELETE_FLAG=0 AND trt.COURSE_ID='" + courseId + "' AND trt.CLASS_ID=" + classId;
        return pageJdbcUtil.getInfo(sql, tr);
    }

    /**
     * 获取章节平均成绩
     *
     * @param courseId
     * @return
     */
    public List<ChapterReportDomain> getChapterTaskAvgScore(String courseId, Long classId) {
//        List<CourseChapter> courseChapterList = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDomain> domains = new ArrayList<>();
//        for (CourseChapter c : courseChapterList) {
//            ChapterReportDomain domain = getChapterTaskScore(c.getId());
//            domain.setChapterName(c.getName());
//            domains.add(domain);
//        }
//        return domains;
        String sql = "SELECT trt.TASK_NAME chapter_name,(SELECT GROUP_CONCAT(ttsi.grade) FROM t_task_stu_infor ttsi WHERE ttsi.task_status='approved' AND ttsi.release_task_id=trt.id) result FROM t_release_task trt  WHERE trt.DELETE_FLAG=0 AND trt.COURSE_ID='" + courseId + "' AND trt.CLASS_ID=" + classId + " ORDER BY trt.CREATED_DATE";
        return pageJdbcUtil.getInfo(sql, chapterReport);
    }

    /**
     * 获取班级活跃度信息
     *
     * @param classId
     * @return
     */
    public ClassDataInfoDomain getClassInfoData(Long classId, Integer day) {
        Classes aClass = classesService.findClass(classId);
        ClassDataInfoDomain domain = new ClassDataInfoDomain();
        domain.setClassName(aClass.getName());
        domain.setStudentNum(getClassStuId(classId).size());
        String sql = "SELECT COUNT(DISTINCT tl.CREATED_BY) FROM t_log tl LEFT JOIN t_user tu ON tu.id=tl.CREATED_BY WHERE tl.DELETE_FLAG=0 AND tu.delete_flag=0 AND tu.CLASS_ID=" + classId;
        domain.setLoginNum(pageJdbcUtil.getCountInt(sql));
        domain.setTotalOnlineTime(getStuTotalOnlineTime(classId, day));
        if (domain.getStudentNum() != 0 && domain != null) {
            domain.setAvgOnlineTime(domain.getTotalOnlineTime() / domain.getStudentNum());
            domain.setLoginPercentage(domain.getLoginNum() * 100 / domain.getStudentNum());
        }
        LoginNumDomain classLoginNum = getClassLoginNum(classId, day);
        Map<String, Integer> map = new HashMap<>();
        map.put("0:00-7:00", classLoginNum.getTime1());
        map.put("7:00-12:00", classLoginNum.getTime2());
        map.put("12:00-14:00", classLoginNum.getTime3());
        map.put("14:00-18:00", classLoginNum.getTime4());
        map.put("18:00-20:00", classLoginNum.getTime5());
        map.put("20:00-22:00", classLoginNum.getTime6());
        map.put("22:00-24:00", classLoginNum.getTime7());
        Set<String> strings = map.keySet();

        for (String s : strings) {
            if (domain.getMaxTime() == null) {
                domain.setMaxTime(s);
            } else if (map.get(s) > map.get(domain.getMaxTime())) {
                domain.setMaxTime(s);
            }

        }


        return domain;
    }

    RowMapper<LoginNumDomain> ln = new RowMapper<LoginNumDomain>() {

        @Override
        public LoginNumDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            LoginNumDomain domain = new LoginNumDomain();
            domain.setTime1(rs.getInt("time1"));
            domain.setTime2(rs.getInt("time2"));
            domain.setTime3(rs.getInt("time3"));
            domain.setTime4(rs.getInt("time4"));
            domain.setTime5(rs.getInt("time5"));
            domain.setTime6(rs.getInt("time6"));
            domain.setTime7(rs.getInt("time7"));

            return domain;
        }
    };

    /**
     * 班级登录次数统计
     *
     * @param classId
     * @param day
     * @return
     */
    public LoginNumDomain getClassLoginNum(Long classId, Integer day) {
        String sql = "SELECT sum(HOUR(t.CREATED_DATE) BETWEEN 0 AND 6) time1,sum(HOUR(t.CREATED_DATE) BETWEEN 7 AND 11) time2,sum(HOUR(t.CREATED_DATE) BETWEEN 12 AND 13) time3,sum(HOUR(t.CREATED_DATE) BETWEEN 14 AND 17) time4,sum(HOUR(t.CREATED_DATE) BETWEEN 18 AND 19) time5,sum(HOUR(t.CREATED_DATE) BETWEEN 20 AND 21) time6,sum(HOUR(t.CREATED_DATE) BETWEEN 22 AND 23) time7 FROM t_log t WHERE t.DELETE_FLAG=0 AND DATE_SUB(NOW(),INTERVAL " + day + " DAY)<t.CREATED_DATE AND t.OPERATION_TYPE='登录' AND t.CREATED_BY in (SELECT t.id FROM t_user t WHERE t.delete_flag=0 AND t.CLASS_ID=" + classId + ")";
        List<LoginNumDomain> info = pageJdbcUtil.getInfo(sql, ln);
        return info.get(0);
    }

    RowMapper<LoginDateNumDomain> ld = new RowMapper<LoginDateNumDomain>() {

        @Override
        public LoginDateNumDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            LoginDateNumDomain domain = new LoginDateNumDomain();
            domain.setDateTime(rs.getString("login_time"));
            domain.setLoginNum(rs.getInt("login_num"));


            return domain;
        }
    };

    public List<LoginDateNumDomain> getLoginNum(Long classId, Integer day) {
        int day1 = day + 1;
        String sql = "SELECT DAY(t.CREATED_DATE) login_time,COUNT(*) login_num FROM t_log t WHERE DATE_SUB(CURDATE(),INTERVAL " + day + " DAY)<t.CREATED_DATE AND t.OPERATION_TYPE='登录' AND t.CREATED_BY in (SELECT t.id FROM t_user t WHERE t.delete_flag=0 AND t.CLASS_ID=" + classId + ")  GROUP BY day(t.CREATED_DATE) ORDER BY t.CREATED_DATE ";
        List<LoginDateNumDomain> info = pageJdbcUtil.getInfo(sql, ld);
        sql = "SELECT @rownum:=@rownum-1 AS rownum,DAY(DATE_SUB(SYSDATE(),INTERVAL @rownum DAY)) as login_time ,0 login_num FROM  (SELECT @rownum:=" + day1 + ") r,t_log t  LIMIT 0," + day;
        List<LoginDateNumDomain> info1 = pageJdbcUtil.getInfo(sql, ld);
        for (int j = 0; j < info1.size(); j++) {
            for (LoginDateNumDomain i : info) {
                if (i.getDateTime().equals(info1.get(j).getDateTime())) {
                    info1.get(j).setLoginNum(i.getLoginNum());
                    continue;
                }

            }

        }
        return info1;
    }

    /**
     * 获取活跃值
     *
     * @param vitality
     * @return
     */
    public Integer getClassVitality(Integer vitality) {
        if (vitality > 120) {
            return 100;
        } else {
            return vitality * 10 / 12;
        }
    }

    /**
     * 教师端实验列表页
     *
     * @param courseId
     * @return
     */
    RowMapper<ChapterReportDataDomain> rm = new RowMapper<ChapterReportDataDomain>() {

        @Override
        public ChapterReportDataDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            ChapterReportDataDomain domain = new ChapterReportDataDomain();
            domain.setCourseClassId(rs.getString("COURSE_CLASS_ID"));
            domain.setId(rs.getString("CHAPER_ID"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            Integer total;
            if (StringUtils.isEmpty(rs.getString("class_name"))) {
                total = rs.getInt("STU_NUM");
            } else {
                total = rs.getInt("class_stu_num");
            }
            if (total != null && total > 0) {
                domain.setCommit(rs.getInt("commit_num") * 100 / total);
            }
            ChapterReportDomain result = getChapterReportScore(rs.getString("result"));
            domain.setAvgScore(result.getReportScore());
            return domain;
        }
    };

    public List<ChapterReportDataDomain> ChapterReportDataDomain(String courseId, Long classId) {
//       Course courseById = courseRepository.findCourseById(courseId);
//       List<CourseChapter> courseChapters = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//       List<ChapterReportDataDomain> domains = new ArrayList<>();
//       for (CourseChapter c : courseChapters) {
//           ChapterReportDataDomain chapterReportDataDomain = new ChapterReportDataDomain();
//           ChapterReportDomain chapterAvgScore = getChapterAvgScore(c.getId());
//           chapterReportDataDomain.setId(courseId);
//           chapterReportDataDomain.setAvgScore(chapterAvgScore.getReportScore());
//           chapterReportDataDomain.setChapterName(c.getName());
//           chapterReportDataDomain.setCourseName(courseById.getName());
//           Integer chapterReportCommitNum = getChapterReportCommitNum(c.getId());
//           Integer chapterReportTotalNum = getChapterReportTotalNum(c.getId());
//           if (chapterReportTotalNum != 0) {
//               chapterReportDataDomain.setCommit(chapterReportCommitNum * 100 / chapterReportTotalNum);
//           } else {
//                chapterReportDataDomain.setCommit(0);
//            }
//            domains.add(chapterReportDataDomain);
//        }
//        return domains;
        String querySql = "select lc.COURSE_ID,lc.COURSE_CLASS_ID,lc.CHAPER_ID,c.`name` as course_name,cl.name as class_name,cc.`name` as chapter_name,(select count(*) from `t_user` u where u.delete_flag =0 and u.CLASS_ID =lc.CLASS_ID) as class_stu_num,lc.STU_NUM,(select count(*) from `t_lab_report` lr where lr.DELETE_FLAG =0 and COMMIT_TIME is not null and lr.COURSE_CLASS_ID=lc.COURSE_CLASS_ID and lr.CHAPER_ID =lc.CHAPER_ID) as commit_num ,(SELECT GROUP_CONCAT(t.REVIEW_RESULT) FROM t_lab_report t WHERE t.CHAPER_ID=cc.id AND t.REVIEW_RESULT not in('init','uncommit','unchecked') AND t.COURSE_CLASS_ID=lc.COURSE_CLASS_ID ) as result from `t_lab_class` lc LEFT JOIN `c_course` c ON lc.COURSE_ID = c.id LEFT JOIN `t_classes` cl ON lc.CLASS_ID = cl.ID LEFT JOIN `c_course_chapter` cc ON cc.id = lc.CHAPER_ID where lc.DELETE_FLAG =0 AND lc.CLASS_ID=" + classId + " and lc.COURSE_ID='" + courseId + "'";
        return pageJdbcUtil.getInfo(querySql, rm);

    }

    /**
     * 任务数据列表页
     *
     * @param courseId
     * @return
     */
    RowMapper<TaskDataDomain> td = new RowMapper<TaskDataDomain>() {

        @Override
        public TaskDataDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            TaskDataDomain domain = new TaskDataDomain();
            domain.setId(rs.getString("id"));
            domain.setCourseName(rs.getString("course_name"));
            domain.setChapterName(rs.getString("chapter_name"));
            ChapterReportDomain result = getChapterReportScore(rs.getString("result"));
            domain.setTaskName(rs.getString("TASK_NAME"));
            domain.setAvgScore(result.getReportScore());
            if (rs.getInt("total_num") > 0) {
                domain.setCommit(rs.getInt("commit_num") * 100 / rs.getInt("total_num"));
            }
            return domain;
        }
    };

    public List<TaskDataDomain> ChapterTaskDataDomain(String courseId, Long classId) {
//        Course courseById = courseRepository.findCourseById(courseId);
//        List<CourseChapter> courseChapters = chapterRepository.findByCourseIdAndDeleteFlagAndParentIdIsNullOrderByOrderNo(courseId, DataValidity.VALID.getState());
//        List<ChapterReportDataDomain> domains = new ArrayList<>();
//        for (CourseChapter c : courseChapters) {
//            ChapterReportDataDomain chapterReportDataDomain = new ChapterReportDataDomain();
//            ChapterReportDomain chapterAvgScore = getChapterTaskScore(c.getId());
//            chapterReportDataDomain.setId(courseId);
//            chapterReportDataDomain.setAvgScore(chapterAvgScore.getReportScore());
//            chapterReportDataDomain.setChapterName(c.getName());
//            chapterReportDataDomain.setCourseName(courseById.getName());
//            Integer chapterReportCommitNum = getChapterTaskCommitNum(c.getId());
//            Integer chapterReportTotalNum = getChapterTaskTotal(c.getId());
//            if (chapterReportTotalNum != 0) {
//                chapterReportDataDomain.setCommit(chapterReportCommitNum * 100 / chapterReportTotalNum);
//            } else {
//                chapterReportDataDomain.setCommit(0);
//            }
//            domains.add(chapterReportDataDomain);
//        }
//        return domains;
        String sql = "SELECT trt.ID, cc.name course_name, ccc.name chapter_name,trt.TASK_NAME TASK_NAME,(SELECT GROUP_CONCAT(ttsi.result) FROM t_task_stu_infor ttsi WHERE ttsi.task_status='approved' AND ttsi.release_task_id=trt.id) result , (SELECT COUNT(*) FROM t_task_stu_infor ttsi WHERE ttsi.delete_flag=0 AND ttsi.release_task_id=trt.id) total_num,trt.COMMIT_NUM commit_num FROM t_release_task trt LEFT JOIN c_course cc ON trt.COURSE_ID=cc.id LEFT JOIN c_course_chapter ccc ON trt.COURSE_CHAPTER_ID=ccc.id  WHERE trt.DELETE_FLAG=0 AND trt.COURSE_ID='" + courseId + "' AND trt.CLASS_ID=" + classId + " ORDER BY trt.CREATED_DATE";
        return pageJdbcUtil.getInfo(sql, td);
    }

    RowMapper<FileLookNumDomain> lookNumDomainRowMapper = new RowMapper<FileLookNumDomain>() {

        @Override
        public FileLookNumDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            FileLookNumDomain domain = new FileLookNumDomain();
            domain.setName(rs.getString(1));
            domain.setNum(rs.getInt(2));
            return domain;
        }
    };

    //内容资源点击统计
    public List<FileLookNumDomain> FileLookNum(QueryStuLookFileDomain domain) {
        String sql = "SELECT cf.name,COUNT(cd.id) FROM c_course_chapter_file cf LEFT JOIN c_file_look_data cd ON cf.id=cd.file_id  WHERE cf.chapter_id=" + domain.getChapterId() + " AND (cd.class_id=" + domain.getClassId() + " or cd.class_id is null) GROUP BY cf.name ";
        return pageJdbcUtil.getInfo(sql, lookNumDomainRowMapper);
    }

    RowMapper<Long> stuIdRm = new RowMapper<Long>() {

        @Override
        public Long mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            return rs.getLong(1);
        }
    };
    RowMapper<String> stuNameRm = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub

            return rs.getString(1);
        }
    };

    public Map<String, Object> fileLook(QueryStuLookFileDomain domain) {
        Pageable pageable = PageUtil.createRequestAndSortType(domain.getPageNumber(), domain.getPageSize(), "desc", "no");
        Page<CourseChapterFile> courseChapterFileList = chapterFileRepository.findByChapterIdAndDeleteFlagOrderByNo(pageable, domain.getChapterId(), DataValidity.VALID.getState());

        List<FileStuLookNumDomain> fileStuLookNumDomainList = new ArrayList<>();
        for (CourseChapterFile chapterFile : courseChapterFileList.getContent()) {
            FileStuLookNumDomain fileStuLookNumDomain = new FileStuLookNumDomain();
            List<Long> info = pageJdbcUtil.getInfo("SELECT DISTINCT d.stu_id FROM  c_file_look_data d  WHERE d.file_id=" + chapterFile.getId() + " AND d.class_id=" + domain.getClassId(), stuIdRm);
            StringBuffer stringBuffer = new StringBuffer();
            for (int i = 0; i < info.size(); i++) {
                stringBuffer.append(info.get(i));
                if (i > info.size() - 1) {
                    stringBuffer.append(",");
                }
            }
            String sql = "SELECT t.name FROM t_user t WHERE t.CLASS_ID=" + domain.getClassId() + " AND t.delete_flag=0 ";
            if (info.size() > 0) {
                sql += "AND t.id NOT in (" + stringBuffer.toString() + ")";
            }

            List<String> nameList = pageJdbcUtil.getInfo(sql, stuNameRm);
            fileStuLookNumDomain.setLookNum(info.size());
            fileStuLookNumDomain.setUnLookNum(nameList.size());
            fileStuLookNumDomain.setStuName(nameList);
            fileStuLookNumDomain.setName(chapterFile.getName());
            fileStuLookNumDomainList.add(fileStuLookNumDomain);
        }
        PageDomain pageDomain = new PageDomain();
        pageDomain.setPageSize(courseChapterFileList.getSize());
        pageDomain.setPageNumber(courseChapterFileList.getNumber());
        pageDomain.setTotalElements(courseChapterFileList.getTotalElements());
        pageDomain.setTotalPages(courseChapterFileList.getTotalPages());
        Map<String, Object> map = new HashMap<>();
        map.put(ApiReturnConstants.SUCCESS, true);
        map.put(ApiReturnConstants.DATA, fileStuLookNumDomainList);
        map.put(ApiReturnConstants.PAGE, pageDomain);
        return map;
    }

    RowMapper<StuLookFileTypeDomain> typeDomainRowMapper = new RowMapper<StuLookFileTypeDomain>() {

        @Override
        public StuLookFileTypeDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            StuLookFileTypeDomain stuLookFileTypeDomain = new StuLookFileTypeDomain();
            stuLookFileTypeDomain.setId(rs.getLong(1));
            stuLookFileTypeDomain.setName(rs.getString(2));
            return stuLookFileTypeDomain;
        }
    };
    RowMapper<typeDomain> typeRowMapper = new RowMapper<typeDomain>() {

        @Override
        public typeDomain mapRow(ResultSet rs, int rowNum)
                throws SQLException {
            // TODO Auto-generated method stub
            typeDomain stuLookFileTypeDomain = new typeDomain();
            stuLookFileTypeDomain.setType(rs.getInt(1));
            stuLookFileTypeDomain.setNum(rs.getInt(2));
            return stuLookFileTypeDomain;
        }
    };

    public Map<String, Object> lookFileTypeDomains(QueryStuLookFileDomain domain) {
        String sql = "SELECT  t.id, t.name FROM t_user t WHERE t.CLASS_ID=" + domain.getClassId() + " AND t.delete_flag=0 ";
        String countSql = "SELECT count(*) FROM t_user t WHERE t.CLASS_ID=" + domain.getClassId() + " AND t.delete_flag=0 ";
        List<SortDTO> sort = new ArrayList<SortDTO>();
        SortDTO dto = new SortDTO();
        dto = new SortDTO();
        dto.setKey("t.CREATED_DATE");
        dto.setAsc(false);
        sort.add(dto);
        Map<String, Object> pageInfor = pageJdbcUtil.getPageInfor(domain.getPageSize(), domain.getPageNumber(), typeDomainRowMapper, sort, sql, countSql);

        List<StuLookFileTypeDomain> info = (List<StuLookFileTypeDomain>) pageInfor.get(ApiReturnConstants.DATA);
        info.forEach(item -> {
            List<typeDomain> info1 = pageJdbcUtil.getInfo("SELECT c.type,COUNT(DISTINCT d.file_id) FROM c_course_chapter_file c LEFT JOIN c_file_look_data d ON c.id=d.file_id WHERE c.chapter_id=" + domain.getChapterId() + " AND d.stu_id=" + item.getId() + " GROUP BY c.type", typeRowMapper);
            Map<Integer, Integer> map = new HashMap<>();
            info1.forEach(e -> {
                map.put(e.getType(), e.getNum());
            });
            item.setPpt(map.get(10) == null ? 0 : map.get(10));
            item.setVideo(map.get(20) == null ? 0 : map.get(20));
            item.setTask(map.get(30) == null ? 0 : map.get(30));
            item.setTask1(map.get(40) == null ? 0 : map.get(40));
            item.setOther(map.get(50) == null ? 0 : map.get(50));
        });
        pageInfor.put(ApiReturnConstants.DATA, info);
        return pageInfor;
    }

    public FileNumDomain getFileTotalNum(Long chapterId) {
        FileNumDomain fileNumDomain = new FileNumDomain();
        String sql = "SELECT type,COUNT(*) FROM c_course_chapter_file WHERE chapter_id=" + chapterId + " GROUP BY type";
        List<typeDomain> info = pageJdbcUtil.getInfo(sql, typeRowMapper);
        Map<Integer, Integer> map = new HashMap<>();
        info.forEach(e -> {
            map.put(e.getType(), e.getNum());
        });
        fileNumDomain.setPptNum(map.get(10) == null ? 0 : map.get(10));
        fileNumDomain.setVideoNum(map.get(20) == null ? 0 : map.get(20));
        fileNumDomain.setTaskNum(map.get(30) == null ? 0 : map.get(30));
        fileNumDomain.setTask1Num(map.get(40) == null ? 0 : map.get(40));
        fileNumDomain.setOtherNum(map.get(50) == null ? 0 : map.get(50));
        return fileNumDomain;
    }

    public UnLookFileDomain getUnLookFile(Long chapterId, Long stuId) {
        UnLookFileDomain unLookFileDomain = new UnLookFileDomain();
        String sql = "SELECT DISTINCT c.name,c.type FROM c_course_chapter_file c LEFT JOIN c_file_look_data d ON c.id=d.file_id WHERE c.chapter_id=" + chapterId + " AND (d.stu_id<>" + stuId + " or d.id is NULL)";
        List<FileLookNumDomain> info = pageJdbcUtil.getInfo(sql, lookNumDomainRowMapper);
        List<String> pptList = new ArrayList<>();
        List<String> videoList = new ArrayList<>();
        List<String> taskList = new ArrayList<>();
        List<String> task1List = new ArrayList<>();
        List<String> otherList = new ArrayList<>();
        info.forEach(i -> {
            if (i.getNum() == 10) {
                pptList.add(i.getName());
            } else if (i.getNum() == 20) {
                videoList.add(i.getName());
            } else if (i.getNum() == 30) {
                taskList.add(i.getName());
            } else if (i.getNum() == 40) {
                task1List.add(i.getName());
            } else if (i.getNum() == 50) {
                otherList.add(i.getName());
            }
        });
        unLookFileDomain.setOtherList(otherList);
        unLookFileDomain.setPptList(pptList);
        unLookFileDomain.setTaskList(taskList);
        unLookFileDomain.setTask1List(task1List);
        unLookFileDomain.setVideoList(videoList);
        return unLookFileDomain;
    }

    public Map<String, Object> getLoginNum(QueryStuLoginDomain domain) {
        Map<String, Object> map = new HashMap<>();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        String format = formatter.format(domain.getDate());
        Pageable pageable = PageUtil.createRequestAndSortType(domain.getPageNumber(), domain.getPageSize(), "desc", "id");
        Page<User> users = userRepository.findByClassIdAndDeleteFlag(pageable, domain.getClassId(), DataValidity.VALID.getState());
        List<StuLoginNumDomain> loginNumDomainList = new ArrayList<>();
        for (User user : users.getContent()) {
            StuLoginNumDomain stuLoginNumDomain = new StuLoginNumDomain();
            stuLoginNumDomain.setName(user.getName());
            stuLoginNumDomain.setTime1(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 08:30:00' AND '" + format + " 10:25:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime2(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 10:40:00' AND '" + format + " 12:10:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime3(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 12:20:00' AND '" + format + " 13:50:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime4(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 14:00:00' AND '" + format + " 15:35:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime5(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 15:50:00' AND '" + format + " 16:35:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime6(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 16:40:00' AND '" + format + " 17:25:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime7(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 17:30:00' AND '" + format + " 18:20:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime8(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 18:30:00' AND '" + format + " 20:10:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime9(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 20:20:00' AND '" + format + " 22:20:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime10(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 22:30:00' AND '" + format + " 23:59:59' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime11(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 00:00:00' AND '" + format + " 02:00:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime12(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 02:10:00' AND '" + format + " 05:00:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTime13(pageJdbcUtil.getCountInt("SELECT COUNT(t.id) FROM t_log t WHERE t.DELETE_FLAG=0 AND t.CREATED_BY=" + user.getId() + " AND t.CREATED_DATE BETWEEN '" + format + " 05:10:00' AND '" + format + " 08:00:00' AND t.OPERATION_TYPE='登录'"));
            stuLoginNumDomain.setTotalNum(stuLoginNumDomain.getTime1() + stuLoginNumDomain.getTime2() + stuLoginNumDomain.getTime3() + stuLoginNumDomain.getTime4() + stuLoginNumDomain.getTime5() + stuLoginNumDomain.getTime6() + stuLoginNumDomain.getTime7() + stuLoginNumDomain.getTime8() + stuLoginNumDomain.getTime9() + stuLoginNumDomain.getTime10() + stuLoginNumDomain.getTime11() + stuLoginNumDomain.getTime12() + stuLoginNumDomain.getTime13());
            loginNumDomainList.add(stuLoginNumDomain);

        }
        PageDomain pageDomain = new PageDomain();
        pageDomain.setPageSize(users.getSize());
        pageDomain.setPageNumber(users.getNumber());
        pageDomain.setTotalElements(users.getTotalElements());
        pageDomain.setTotalPages(users.getTotalPages());
        map.put(ApiReturnConstants.DATA, loginNumDomainList);
        map.put(ApiReturnConstants.PAGE, pageDomain);
        return map;
    }
}
